package com.saozhu.Dao;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.saozhu.pojo.Good;
import com.saozhu.pojo.Player;
import com.saozhu.pojo.ReceivceInformation;
import com.saozhu.utils.Console;


public class DataBase {

	private static DataBase database = new DataBase();
	private Player customer;
	private DataBase() {}
	public static DataBase getInstance() {
		return database;
	}
	public Player getCustomer() {
		return customer;
	}
	public void setCustomer(Player customer) {
		this.customer = customer;
	}
	public void newOrder(Player customer,int num,Good good) {
		
		String sql = "insert into `order`(order_code, price, Goods_num, Goods_id, user_id, receive_information_id)"
				+ "values(?,?,?,?,?,?)"; 
		Connection c = null;
		PreparedStatement ps = null;

		try {
			c = getConnection();
			ps = c.prepareStatement(sql);
			ps.setInt(1, Console.randomNum());
			ps.setFloat(2, num*good.getPrice());
			ps.setInt(3, num);
			ps.setInt(4, good.getId());
			ps.setInt(5, customer.getId());	
//			ReceivceInformation r = new ReceivceInformation(1,"17816876666",3);
//			customer.setReceive();
			ps.setInt(6,customer.getReceive().getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(c,ps,null);
		}
	}
	public ReceivceInformation selectAddress(int id) {
		String sql="select `id`,`address`,`name`,`phone_number` from receive_information "
				+ "where `user_id`=? and id = ?";
		try {
			Connection c = getConnection();
			PreparedStatement ps =c.prepareStatement(sql);			
			ps.setInt(1, customer.getId());
			ps.setInt(2, id);
			ResultSet rs=ps.executeQuery();			
			ReceivceInformation ri =new ReceivceInformation();
			if(rs.next())
			{				
				ri.setId(rs.getInt(1));
				ri.setAddress(rs.getString(2));
				ri.setName(rs.getString(3));
				ri.setPhoneNumber(rs.getString(4));
			}
			return ri;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
//	public void payOrder(int id) {		
//		String sql = "update order set pay = 'Y' where id = ?"; 
//		Connection c = null;
//		PreparedStatement ps = null;
//		try {
//			c = getConnection();
//			ps = c.prepareStatement(sql);
//			c.prepareStatement(sql);
//			ps.setInt(1, id);
//			ps.executeUpdate();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			clean(c,ps,null);
//		}
//	}
	
	public void payOrder(Player customer) {		
		String sql = "update `order` set pay = 'Y' where user_id = ?"; 
		Connection c = null;
		PreparedStatement ps = null;
		try {
			c = getConnection();
			ps = c.prepareStatement(sql);
			ps.setInt(1, customer.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(c,ps,null);
		}
	}
	
	public void showOwnerOrder(Player customer) {		
		String sql = "select * from `order` o,`user` u,`goods` g,`receive_information` r"
				+ " where g.id = o.Goods_id and "
				+ " u.id = o.user_id and"
				+ " r.id = o.receive_information_id and "
				+ " u.id = ?"; 
		Connection c = null;
		//TODO 
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			c = getConnection();
			ps = c.prepareStatement(sql);
			ps.setInt(1, customer.getId());
			rs = ps.executeQuery();
			while(rs.next()) {				
				Console.print("--oder_code:"+rs.getString("id"));
				Console.print("--pay:"+rs.getString("pay"));
				Console.print("--price:"+rs.getString("price"));
				Console.print("--Good_num:"+rs.getString("Goods_num"));
				Console.print("--finish:"+rs.getString("finish"));
				Console.print("--Good_name:"+rs.getString("g.name"));
				Console.println("--recieveInformation:"+rs.getInt("receive_information_id"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(c,ps,null);
		}
	}
	
//	public void showAllOrder(Customer customer) {		
//		String sql = "select * from order where user_id = ?"; 
//		Connection c = null;
//		PreparedStatement ps = null;
//		ResultSet rs = null;
//		try {
//			c = getConnection();
//			ps = c.prepareStatement(sql);
//			ps.setInt(1,customer.getId());
//			rs = ps.executeQuery();
//			while(rs.next()) {
//				
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			clean(c,ps,null);
//		}
//	}
	
//	public void deleteOrder(int id) {		
//		String sql = "update order set open = 'N' where id = ?"; 
//		Connection c = null;
//		PreparedStatement ps = null;
//		try {
//			c = getConnection();
//			ps = c.prepareStatement(sql);
//			ps.setInt(1, id);
//			ps.executeUpdate();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			clean(c,ps,null);
//		}
//	}
//	
//	public void deleteAllOrder(Customer customer) {		
//		String sql = "update order set open = 'N' where user_id = ?"; 
//		Connection c = null;
//		PreparedStatement ps = null;
//		try {
//			c = getConnection();
//			ps = c.prepareStatement(sql);
//			ps.setInt(1, customer.getId());
//			ps.executeUpdate();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			clean(c,ps,null);
//		}
//	}
	
	public void showOwner(Player customer) {		
		String sql = "select * from `user` u,`client` c where u.id = ?;"; 
		Connection c = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			c = getConnection();
			ps = c.prepareStatement(sql);
			ps.setInt(1, customer.getId());
			rs = ps.executeQuery();
			while(rs.next()) {
				customer.setId(rs.getInt("id"));
				customer.setName(rs.getString("name"));
				customer.setCredit(rs.getInt("credit"));
				customer.setVip(rs.getString("vip"));
				customer.setPhone(rs.getString("phone"));
				Console.println(customer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(c,ps,null);
		}
	}
	
	public List<Good> showGood() {		
		String sql = "select * from `Goods`"; 
		Connection c = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Good> list = new ArrayList<>();
		Good good = new Good();
		try {
			c = getConnection();
			ps = c.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				good.setId(rs.getInt("id"));
				good.setName(rs.getString("name"));
				good.setNumber(rs.getInt("number"));
				good.setPrice(rs.getInt("price"));
				list.add(good);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(c,ps,null);
		} return null;
	}
	
	public Good selectGood(String name) {		
		String sql = "select * from `Goods` where name = ?"; 
		Connection c = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		Good good = new Good();
		try {
			c = getConnection();
			ps = c.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			while(rs.next()) {
				good.setId(rs.getInt("id"));
				good.setName(name);
				good.setNumber(rs.getInt("number"));
				good.setPrice(rs.getInt("price"));
			}
			return good;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(c,ps,null);
		} return null;
	}
	
	public static void loadDriver() {
		try {
			Class.forName(getProperty("jdbc.driver"));		
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection() throws SQLException {

		Connection c = null;			
		String url = getProperty("url");
		String user = getProperty("user");
		String password = getProperty("password");
		c = DriverManager.getConnection(url, user, password);
		return c;

	}
	private static String getProperty(String key) {

		Properties pros = new Properties();
		try {
			pros.load(new FileInputStream("conn.properties"));
			return pros.getProperty(key);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void clean(Connection c, Statement sttm, ResultSet rs) {
		if(rs!=null) {
			try {
				c.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(sttm!=null) {
			try {
				c.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(c!=null) {
			try {
				c.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	
}
